# !usr/bin/env python
# -*- coding:utf-8 _*-
"""
@Author:张广勤
@Web site: https://www.tunan.wang
@Github:www.github.com
 
@File:modi_ziduan1_0.py
@Time:2024/12/3 8:45

@Motto:不积跬步无以至千里，不积小流无以成江海！
"""
import pymysql
import re

# 数据库连接信息
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '12345',
    'db': 'yanbei2023',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

# 需要修改的数据表名
table_name = '2023交通补贴汇总表'

# 连接到数据库
connection = pymysql.connect(**db_config)

try:
    with connection.cursor() as cursor:
        # 获取数据表的字段信息
        cursor.execute(f"SHOW COLUMNS FROM {table_name}")
        columns = cursor.fetchall()

        # 生成新的字段名及其对应的 ALTER TABLE 语句
        alter_statements = []
        for column in columns:
            old_column_name = column['Field']
            # 将 `.` 和 `(` 替换为 `_` 并去掉不需要的字符（例如空格）
            # 中文括号
            new_column_name = re.sub(r'[.\s\（\）]+', '_', old_column_name).strip('_')

            if old_column_name != new_column_name:
                # 构造 ALTER TABLE 语句的 CHANGE COLUMN 部分
                column_definition = f"{column['Type']}"
                if not column['Null']:
                    column_definition += " NOT NULL"
                if column['Default'] is not None:
                    column_definition += f" DEFAULT {pymysql.escape_string(column['Default'])}"
                if column['Extra'] and 'auto_increment' in column['Extra'].lower():
                    column_definition += " AUTO_INCREMENT"

                alter_statements.append(
                    f"CHANGE `{old_column_name}` `{new_column_name}` {column_definition}"
                )

        # 执行 ALTER TABLE 语句
        if alter_statements:
            alter_table_sql = f"ALTER TABLE {table_name} " + ", ".join(alter_statements)
            print("Executing ALTER TABLE statement:")
            print(alter_table_sql)
            cursor.execute(alter_table_sql)
            connection.commit()
        else:
            print("No changes needed.")

finally:
    connection.close()